package pts.salooner.data.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.log4j.Logger;

import pts.salooner.data.DaoUtil;
import pts.salooner.data.DataConfig;
import pts.salooner.data.SaloonerVo;
import pts.salooner.data.Constant.SaloonerConstant;
import pts.salooner.data.exception.SaloonerDaoException;
import pts.salooner.data.filter.SaloonerDaoFilter;

public class SaloonerDao {

	private static final String COLUMN_ID = "ID";
	private static final String COLUMN_FIRSTNAME = "FIRSTNAME";
	private static final String COLUMN_LASTNAME = "LASTNAME";

	private static Logger logger = Logger.getLogger(SaloonerDao.class);
	

	private String sqlSaloonerdbCreate;
	private String sqlSaloonerdbGet;
	private String sqlSaloonerdbGetById;
	private String sqlSaloonerdbUpdateById;
	private String sqlSaloonerdbDeleteById;
	private String sqlSaloonerdbFilterGetById;
	private String sqlSaloonerdbFilterGetByFirstname;
	private String sqlSaloonerdbFilterGetByLastname;

	private DaoUtil util = DaoUtil.getDaoUtil();

	public SaloonerDao() {
		this.initialize();
	}

	/**
	 * Creates a new object into the database
	 * 
	 * @param vo
	 */
	public void create(SaloonerVo vo) {

		if (vo != null) {
			Object[] args = { vo.getId(), vo.getLastname(), vo.getFirstname() };
			Integer[] sqlType = { Types.INTEGER, Types.VARCHAR, Types.VARCHAR };

			util.executePreparedStatementUpdate(sqlSaloonerdbCreate,
					Arrays.asList(args), Arrays.asList(sqlType));
		} else {
			logger.info("Trying to create <null> object in database --> nothing created");
		}
	}

	/**
	 * Update the object whose ID matches the parameter vo's id.
	 * 
	 * @param vo
	 * @throws SaloonerDaoException
	 */
	public void update(SaloonerVo vo) throws SaloonerDaoException {
		if (vo != null && vo.getId() == 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("ID not specified in update method");
			}
			throw new SaloonerDaoException(
					"Error in ID not found in update method");
		}

		Object[] args = { vo.getLastname(), vo.getFirstname(), vo.getId() };
		Integer[] sqlType = { Types.VARCHAR, Types.VARCHAR, Types.INTEGER };

		util.executePreparedStatementUpdate(sqlSaloonerdbUpdateById,
				Arrays.asList(args), Arrays.asList(sqlType));
	}

	/**
	 * Deletes the object with the givent Id
	 * 
	 * @param id
	 */
	public void delete(int id) {

		Object[] args = { id };
		Integer[] sqlType = { Types.INTEGER };

		util.executePreparedStatementUpdate(sqlSaloonerdbDeleteById,
				Arrays.asList(args), Arrays.asList(sqlType));
	}

	/**
	 * Returns the object identified by "id".
	 * 
	 * @param id
	 * @return
	 */
	public SaloonerVo get(int id) {
		SaloonerVo saloonerVo = null;

		Object[] args = { id };
		Integer[] sqlType = { java.sql.Types.INTEGER };

		ResultSet result = util.executePreparedStatementQuery(
				sqlSaloonerdbGetById, Arrays.asList(args),
				Arrays.asList(sqlType));

		if (result != null) {
			List<SaloonerVo> vos = getVos(result);

			if (vos != null && vos.size() == 1) {
				saloonerVo = vos.get(0);
			} else {
				logger.info("Unable to interpret result. The returned list is either empty or ownes more than one item.");
			}
		}

		return saloonerVo;

	}

	public List<SaloonerVo> get(SaloonerDaoFilter filter) {

		List<SaloonerVo> saloonerVos = null;
		List<Object> args = new ArrayList<Object>();
		List<Integer> sqlType = new ArrayList<Integer>();

		StringBuilder query = new StringBuilder(sqlSaloonerdbGet);

		// filter Id
		if (filter.getId() != null) {
			query = this.appendQuery(query, sqlSaloonerdbFilterGetById,
					SaloonerConstant.SQL_WHERE);
			args.add(filter.getId());
			sqlType.add(Types.INTEGER);
		}

		// filter firstname
		if (filter.getFirstname() != null) {
			query = this.appendQuery(query, sqlSaloonerdbFilterGetByFirstname,
					SaloonerConstant.SQL_AND);
			args.add(filter.getFirstname());
			sqlType.add(Types.VARCHAR);
		}

		// filter lastname
		if (filter.getLastname() != null) {
			query = this.appendQuery(query, sqlSaloonerdbFilterGetByLastname,
					SaloonerConstant.SQL_AND);
			args.add(filter.getLastname());
			sqlType.add(Types.VARCHAR);
		}

		ResultSet result = util.executePreparedStatementQuery(query.toString(),
				args, sqlType);

		if (result != null) {
			saloonerVos = getVos(result);
		}

		return saloonerVos;

	}

	/**
	 * Returns a list of all object from the database.
	 * 
	 * @return
	 */
	public List<SaloonerVo> getAll() {
		List<SaloonerVo> saloonerVos = null;

		ResultSet result = util.executePreparedStatementQuery(sqlSaloonerdbGet);

		if (result != null) {
			saloonerVos = getVos(result);
		}

		return saloonerVos;

	}
	
	
	/**
	 * initialize externalized queries loaded from the file "dao.properties".
	 */
	private void initialize(){
		final DataConfig config = DataConfig.getDataConfig();
		this.sqlSaloonerdbCreate = config.getProperties("SaloonerDao.sqlCreate");
		this.sqlSaloonerdbGet =  config.getProperties("SaloonerDao.sqlGet");
		this.sqlSaloonerdbGetById = config.getProperties("SaloonerDao.sqlGetById");
		this.sqlSaloonerdbUpdateById = config.getProperties("SaloonerDao.sqlUpdateById");
		this.sqlSaloonerdbDeleteById = config.getProperties("SaloonerDao.sqlDeleteById");
		this.sqlSaloonerdbFilterGetById = config.getProperties("SaloonerDao.sqlFilterGetById");
		this.sqlSaloonerdbFilterGetByFirstname = config.getProperties("SaloonerDao.sqlFilterGetByFirstname");
		this.sqlSaloonerdbFilterGetByLastname = config.getProperties("SaloonerDao.sqlFilterGetByLastname");
		
	}
	
	
	
	private List<SaloonerVo> getVos(ResultSet result) {
		List<SaloonerVo> vos = new ArrayList<SaloonerVo>();

		try {
			while (result.next()) {
				SaloonerVo vo = new SaloonerVo();

				vo.setId(result.getInt(COLUMN_ID));
				vo.setFirstname(result.getString(COLUMN_FIRSTNAME));
				vo.setLastname(result.getString(COLUMN_LASTNAME));

				vos.add(vo);
			}
		} catch (SQLException e) {
			if (logger.isDebugEnabled()) {
				logger.debug("Error on extracting list of resultset", e);
			}
		}

		return vos;
	}
	
	
	private StringBuilder appendQuery(StringBuilder query, String filterParam, String sqlClause) {
		// Check if the key word "where" is in the query.
		if(!query.toString().contains(SaloonerConstant.SQL_WHERE)){
			sqlClause = SaloonerConstant.SQL_WHERE;
		}
		return query.append(" " + sqlClause + " " + filterParam) ;
	}
}
